﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Xml.Linq;
using System.Text;
using System.Diagnostics;
using System.Data.SqlClient;
using System.Data; 

namespace Pakxel.Framework
{ 
    sealed class DatabaseConnection : IDatabaseConnection
    {
        XElement root;
        public DatabaseConnection(XElement root)
        {
            this.root = root;
        }

        public DatabaseConnection()
        {
            // TODO: Complete member initialization
        }
        public string ConnectionString(string Id)
        {
            StringBuilder sb;
            var XmlConnectionString = from Connections in root.Descendants("ConnectionStrings").Descendants("ConnectionString")
                                      //let A = ((string)Connections.Attribute("default")) 
                                      let B = ((string)Connections.Element("Id"))
                                      where B.Equals(Id) //&& A.Equals("true")
                                      select Connections;
            sb = new StringBuilder();
            if (XmlConnectionString != null)
            {
                foreach (XElement e in XmlConnectionString)
                {
                    sb.AppendFormat("Data Source={0};Initial Catalog={1};User Id={2};Password={3};", (string)e.Element("DataSource"),
                                    (string)e.Element("Database"), (string)e.Element("UserName"), (string)e.Element("Password"));
                }
            }
            return sb.ToString();
        }

        public static DataTable GetDataTable(string ConnectionString, string SP)
        {
            DataTable dt;
            using (SqlConnection sqlConnection = new SqlConnection(ConnectionString))
            {
                using (SqlCommand storedProcCommand = new SqlCommand(SP, sqlConnection))
                {
                    storedProcCommand.CommandType = System.Data.CommandType.StoredProcedure;
                    sqlConnection.Open();
                    using (SqlDataAdapter da = new SqlDataAdapter(storedProcCommand))
                    {
                        dt = new DataTable();
                        da.Fill(dt);
                    }
                }
            }
            return dt;
        }

        /// <summary>Devuelve el resultado de una consulta a la DB por medio  de un Store Procedure</summary>
        /// <param name="ConnectionString">Cadena de conexion</param>
        /// <param name="SP">Nombre del Store Procedure</param>
        /// <param name="Parametros">Arreglo de Parametros que acepta el Store Procedure</param>
        /// <returns>Datatable resultante</returns>
        public static DataTable GetDataTable(string ConnectionString, string SP, SqlParameter[] Parametros)
        {
            DataTable dt = new DataTable();
            using (SqlConnection sqlConnection = new SqlConnection(ConnectionString))
            {
                try
                {
                    using (SqlDataAdapter da = new SqlDataAdapter(SP, sqlConnection))
                    {
                        da.SelectCommand.CommandType = CommandType.StoredProcedure;

                        foreach (SqlParameter Param in Parametros)
                            da.SelectCommand.Parameters.Add(Param);

                        sqlConnection.Open();

                        da.Fill(dt);
                    }
                }
                catch (SqlException ex) { throw ex; }
                finally { sqlConnection.Close(); }
            }
            return dt;
        }
    }
}
